﻿using System;
using System.Collections.Generic;
using System.Data;
using ContractManager.Common.Class;
using ContractManager.Entity;
using Speed.Data;

namespace ContractManager.BLL.Business
{
    public static class ClsContracts
    {
        /// <summary>
        /// Get All Contracts
        /// </summary>
        /// <returns></returns>
        public static List<Contracts> SelectAll()
        {
            using (Database db = Sys.NewDb())
            {
                return Data.Base.Contracts.Select(db);
            }
        }

        /// <summary>
        /// Get Lookup Contracts
        /// </summary>
        /// <returns></returns>
        public static DataTable GetLookupAll()
        {
            using (Database db = Sys.NewDb())
            {
                string commandText = String.Format("EXEC dbo.usp_Contracts_Search");
                return db.ExecuteDataTable(commandText);
            }
        }

        /// <summary>
        /// Get Contracts by FormNo And Date
        /// </summary>
        /// <param name="formNo"></param>
        /// <param name="voucherCate"></param>
        /// <param name="fromDate"></param>
        /// <param name="toDate"></param>
        /// <returns></returns>
        public static DataTable GetAll(int formNo, int voucherCate, DateTime fromDate, DateTime toDate)
        {
            using (Database db = Sys.NewDb())
            {
                string commandText = String.Format("EXEC dbo.usp_Contracts_GetAll {0},{1},{2},{3},'{4}','{5}'",
                                                   ClsVariable.AsSysMyCoYearId,
                                                   formNo,
                                                   voucherCate,
                                                   ClsVariable.EmployeeId,
                                                   fromDate.ToString("dd/MMM/yyyy"),
                                                   toDate.ToString("dd/MMM/yyyy"));
                return db.ExecuteDataTable(commandText);
            }
        }

        /// <summary>
        /// Tìm kiếm thông tin hợp đồng
        /// </summary>
        /// <param name="formNo"></param>
        /// <param name="voucherCate"></param>
        /// <param name="fromDate">Từ ngày</param>
        /// <param name="toDate">Đến ngày</param>
        /// <param name="conNo">Số hợp đồng</param>
        /// <param name="conName">Tên hợp đồng</param>
        /// <param name="conDescription">Trích dẫn hợp đồng</param>
        /// <param name="conAmountFrom">Giá trị hợp đồng từ</param>
        /// <param name="conAmountTo">Đến</param>
        /// <param name="conPartA">Chủ đầu tư</param>
        /// <param name="conTypId">Loại hợp đồng</param>
        /// <returns></returns>
        public static DataTable Find(int formNo, int voucherCate,
                                     DateTime fromDate, DateTime toDate,
                                     string conNo, string conName,
                                     string conDescription, decimal conAmountFrom,
                                     decimal conAmountTo, int conPartA, int conTypId)
        {
            using (Database db = Sys.NewDb())
            {
                string commandText = String.Format("EXEC dbo.usp_Contracts_Find @FormNo={0}," +
                                                   "@VoucherCate={1},@FromDate='{2}',@ToDate='{3}'," +
                                                   "@ConNo='{4}',@ConName='{5}',@ConDescription='{6}'," +
                                                   "@ConAmountFrom={7}, @ConAmountTo={8},@ConPartA={9},@ConTypId={10}",
                                                   formNo,
                                                   voucherCate,
                                                   fromDate.ToString("dd/MMM/yyyy"),
                                                   toDate.ToString("dd/MMM/yyyy"),
                                                   conNo,
                                                   conName,
                                                   conDescription,
                                                   conAmountFrom,
                                                   conAmountTo,
                                                   conPartA,
                                                   conTypId
                                                   );
                return db.ExecuteDataTable(commandText);
            }
        }

       
        /// <summary>
        /// Get Contract detail by ConId
        /// </summary>
        /// <param name="conId"></param>
        /// <returns></returns>
        public static DataTable ListDetail(Int32 conId)
        {
            using (Database db = Sys.NewDb())
            {
                string commandText = String.Format("EXEC dbo.usp_ContractDetails_SelectByMaster {0}", conId);
                return db.ExecuteDataTable(commandText);
            }
        }

        public static DataTable ListDetailByMbId(Int32 mbId)
        {
            using (Database db = Sys.NewDb())
            {
                string commandText = String.Format("EXEC dbo.usp_ContractDetails_GetByMBId {0}", mbId);
                return db.ExecuteDataTable(commandText);
            }
        }

        /// <summary>
        /// Add New Contracts
        /// </summary>
        /// <param name="capital"></param>
        public static void Insert(Contracts capital)
        {
            using (Database db = Sys.NewDb())
            {
                db.BeginTransaction();
                db.Insert<Contracts>(capital, EnumSaveMode.Requery);

                foreach (ContractDetails addItem in capital.ListAdd)
                {
                    addItem.ConId = capital.ConId;
                    db.Insert<ContractDetails>(addItem);
                }
                foreach (ContractDetails updateItem in capital.ListUpdate)
                {
                    updateItem.ConId = capital.ConId;
                    db.Update<ContractDetails>(updateItem);
                }
                foreach (ContractDetails delItem in capital.ListDelete)
                {
                    db.Delete<ContractDetails>(string.Format("ConDetId={0}", delItem.ConDetId));
                }

                if (capital.ListCategory.Count > 0)
                {
                    db.Delete<ContractCategory>(string.Format("ConId={0}", capital.ConId));
                    foreach (ContractCategory cate in capital.ListCategory)
                    {
                        cate.ConId = capital.ConId;
                        db.Insert<ContractCategory>(cate, EnumSaveMode.Requery);
                    }
                    
                }
                db.Commit();
            }
        }

        /// <summary>
        /// Edit Contracts
        /// </summary>
        /// <param name="capital"></param>
        public static void Update(Contracts capital)
        {
            using (Database db = Sys.NewDb())
            {
                db.BeginTransaction();
                db.Update<Contracts>(capital);
                foreach (ContractDetails addItem in capital.ListAdd)
                {
                    addItem.ConId = capital.ConId;
                    db.Insert<ContractDetails>(addItem);
                }
                foreach (ContractDetails updateItem in capital.ListUpdate)
                {
                    updateItem.ConId = capital.ConId;
                    db.Update<ContractDetails>(updateItem);
                }
                foreach (ContractDetails delItem in capital.ListDelete)
                {
                    db.Delete<ContractDetails>(string.Format("ConDetId={0}", delItem.ConDetId));
                }
                if (capital.ListCategory.Count > 0)
                {
                    db.Delete<ContractCategory>(string.Format("ConId={0}", capital.ConId));
                    foreach (ContractCategory cate in capital.ListCategory)
                    {
                        cate.ConId = capital.ConId;
                        db.Insert<ContractCategory>(cate, EnumSaveMode.Requery);
                    }

                }
                db.Commit();
            }
        }

        /// <summary>
        /// Get Contracts Information
        /// </summary>
        /// <param name="conId"></param>
        /// <returns></returns>
        public static Contracts SelectByPk(Int32 conId)
        {
            using (Database db = Sys.NewDb())
            {
                return db.SelectSingle<Contracts>(string.Format("ConId={0}", conId));
            }
        }

        /// <summary>
        /// Delete Contract Information
        /// </summary>
        /// <param name="conId"></param>
        /// <returns></returns>
        public static int DeleteByPk(Int32 conId)
        {
            using (Database db = Sys.NewDb())
            {
                db.BeginTransaction();
                int ret = db.Delete<Contracts>(string.Format("ConId={0}", conId));
                int rowCount = db.Delete<ContractDetails>(string.Format("ConId={0}", conId));
                db.Commit();
                return ret;
            }
        }

        /// <summary>
        /// Synch RevenueDetails Information with Contracts Details
        /// </summary>
        /// <param name="conId"></param>
        public static void SynchContracts(Int32 conId)
        {
            using (Database db = Sys.NewDb())
            {
                string commandText = String.Format("EXEC dbo.usp_Synch_Contracts {0}", conId);

                int row = db.ExecuteInt32(commandText);
                
                //db.ExecuteNonQuery(commandText, CommandType.StoredProcedure);
            }
        }

        
    }
}